Use Master
go
if Exists(select *From Sys.databases Where name='CafeApp')
Drop database CafeApp
go
create database CafeApp on
(
	name = CafeApp_root,
	Filename = 'D:\CafeApp.mdf',
	size = 100MB,
	maxsize = 10240MB,
	filegrowth = 100kb
)
log on
(
	name = CafeApp_data,
	Filename = 'D:\CafeApp.ldf',
	size = 100MB,
	maxsize = 10240MB,
	filegrowth = 10MB
)
go
Use CafeApp
go
--format thoi gian--
set dateformat dmy

---------------------------------------------------------- Nhân Viên
if Exists(select *From Sys.tables Where name='NhanVien')
Drop table NhanVien
go
create table NhanVien
(
	Manv		nvarchar(10) not null,
	Hoten		nvarchar(50),
	Ngaysinh	date,
	Gioitinh	bit,
	Cmnd		nvarchar(10),
	Phone		nvarchar(11),
	Email		nvarchar(30),
	Hinhanh		Image,		
	Diachi		nvarchar(255),
	Nghiviec	bit,
	Trangthai	bit,
	constraint p_nv primary key (manv)
	--constraint f_clv foreign key (maca) references Calamviec on delete cascade on update cascade
)
go
insert into NhanVien values
('NV00000001',N'Nguyễn Tính','1/1/1992',1,'212701357','01212181202','abc@mail.com','',N'Vũng Tàu',0,0),
('NV00000002',N'Quế Đô','2/2/1992',0,'212701356','01643467841','abc@mail.com','',N'Hồ Chí Minh',0,0),
('NV00000003',N'Hoàng An','1/1/1992',1,'212701357','01212181202','abc@mail.com','',N'Quảng Ngãi',0,0),
('NV00000004',N'Đặng Tâm','2/2/1992',0,'212701356','01643467841','abc@mail.com','',N'Cần Thơ',0,0),
('NV00000005',N'Ưng Hoàng Phúc','1/1/1992',1,'212701357','01212181202','abc@mail.com','',N'Cà Mau',0,0)
go
-----------------------------------PROC-----------------------------
create proc proc_select_all_employees
as
begin
	select * from NhanVien 
end
-----------------------------------------------
go
create proc proc_insert_Image
(
	@hinhanh	Image,
	@Manv		nvarchar(10)
)
as
begin
	Update NhanVien set Hinhanh = @hinhanh where Manv = @Manv
end
go
create proc proc_insert_NhanVien
(
	@Manv		nvarchar(10),
	@Hoten		nvarchar(50)
)
as
begin
	insert into NhanVien values (@Manv,@Hoten,'1/1/1992',1,'212701357','01212181202','abc@mail.com','',N'Cà Mau',0,0)
end
go
create proc proc_select_NhanVien_TheoMa
(
	@Manv		nvarchar(10)
)
as
begin
	select * from NhanVien where Manv = @Manv
end
select * from NhanVien